

set ansi_padding, ansi_warnings, concat_null_yields_null, arithabort, quoted_identifier, ansi_nulls on
go

if exists (select * from tempdb..sysobjects where id=object_id('tempdb..#tmpProperties')) drop table #tmpProperties
go

create table #tmpProperties ( name nvarchar(255), value nvarchar(255) )
go

insert into #tmpProperties values('script', 'update.0018.sql')
insert into #tmpProperties values('error', '0')

set xact_abort on
go

set transaction isolation level serializable
go

begin transaction
go

set ansi_nulls on
set quoted_identifier on   
go
/* BEGIN UPDATE SCRIPT */

--=======================================================
-- SCRIPT TO CREATE CALL REASON MODULE
--=======================================================

------- CREATE MODULE AND SUBMENU --------------
DELETE MODULES WHERE MODULE_NAME='CallReasons'
go
INSERT INTO [dbo].[MODULES]([ID], [DELETED], [CREATED_BY], [DATE_ENTERED], [MODIFIED_USER_ID], [DATE_MODIFIED], [DATE_MODIFIED_UTC], [MODULE_NAME], [DISPLAY_NAME], [RELATIVE_PATH], [MODULE_ENABLED], [TAB_ENABLED], [MOBILE_ENABLED], [TAB_ORDER], [PORTAL_ENABLED], [CUSTOM_ENABLED], [REPORT_ENABLED], [IMPORT_ENABLED], [SYNC_ENABLED], [IS_ADMIN], [CUSTOM_PAGING], [MASS_UPDATE_ENABLED], [DEFAULT_SEARCH_ENABLED], [TABLE_NAME], [EXCHANGE_SYNC], [EXCHANGE_FOLDERS], [EXCHANGE_CREATE_PARENT])
SELECT newid(), 0, NULL, '20111210 16:37:20.643', NULL, '20111210 16:37:20.643', '20111210 16:37:20.643', N'CallReasons', N'.moduleList.CallReasons', N'~/CallReasons/', 1, 1, 1, 40, 0, 0, 0, 0, 0, 1, 1, 0, 1, N'REASONS', 0, 0, 0
go
delete dbo.SHORTCUTS where MODULE_NAME='CallReasons'
INSERT INTO [dbo].[SHORTCUTS]([ID], [DELETED], [CREATED_BY], [DATE_ENTERED], [MODIFIED_USER_ID], [DATE_MODIFIED], [DATE_MODIFIED_UTC], [MODULE_NAME], [DISPLAY_NAME], [RELATIVE_PATH], [IMAGE_NAME], [SHORTCUT_ENABLED], [SHORTCUT_ORDER], [SHORTCUT_MODULE], [SHORTCUT_ACLTYPE])
SELECT newid(), 0, NULL, getdate(), NULL, getdate(), getdate(), N'CallReasons', N'CallReasons.LNK_CALL_REASON_LIST', N'~/CallReasons/default.aspx', N'Campaigns.gif', 1, 1, N'CallReasons', N'edit' UNION ALL
SELECT newid(), 0, NULL, getdate(), NULL, getdate(), getdate(), N'CallReasons', N'CallReasons.LNK_NEW_CALL_REASON', N'~/CallReasons/edit.aspx', N'CreateCampaigns.gif', 1, 1, N'CallReasons', N'edit'
GO
--------------  GRID VIEW ----------------------------------
DELETE DBO.GRIDVIEWS WHERE Module_name='CallReasons'
GO
INSERT INTO [dbo].[GRIDVIEWS]
           (ID
           ,[DELETED]
           ,[CREATED_BY]
           ,[DATE_ENTERED]
           ,[MODIFIED_USER_ID]
           ,[DATE_MODIFIED]
           ,[DATE_MODIFIED_UTC]
           ,[NAME]
           ,[MODULE_NAME]
           ,[VIEW_NAME]
           ,[PRE_LOAD_EVENT_ID]
           ,[POST_LOAD_EVENT_ID])
     VALUES
           (newid()
           ,0
           ,null
           ,getdate()
           ,null
           ,getdate()
           ,getdate()
           ,'CallReasons.ListView'
           ,'CallReasons'
           ,'vwReasons_List'
           ,null
           ,null)
GO
DELETE GRIDVIEWS_COLUMNS WHERE [GRID_NAME]='CallReasons.ListView'
GO
INSERT INTO [dbo].[GRIDVIEWS_COLUMNS]
           ([ID]
           ,[DELETED]
           ,[DATE_ENTERED]
           ,[DATE_MODIFIED]
           ,[DATE_MODIFIED_UTC]
           ,[GRID_NAME]
           ,[COLUMN_INDEX]
           ,[COLUMN_TYPE]
           ,[DEFAULT_VIEW]
           ,[HEADER_TEXT]
           ,[SORT_EXPRESSION]
           ,[ITEMSTYLE_WIDTH]
           ,[ITEMSTYLE_CSSCLASS]
           ,[ITEMSTYLE_WRAP]
           ,[DATA_FIELD]
           ,[DATA_FORMAT]
           ,[URL_FIELD]
           ,[URL_FORMAT]
           )
     VALUES  
           (newid()
           ,0
           ,getdate()
           ,getdate()
           ,getdate()
           ,'CallReasons.ListView'
           ,1
           ,'TemplateColumn'
           ,0
           ,'CallReasons.LBL_LIST_NAME'
           ,'NAME'
           ,'25%'
           ,'listViewTdLinkS1'
           ,1
           ,'NAME'
           ,'Hyperlink'
           ,'ID'
           ,'~/CallReasons/edit.aspx?id={0}')
GO


---------------------------------------------------------------------------
--------------- CREATE DB VIEWS: GENERAL, EDIT, LIST ---------------------- 

drop view [dbo].vwReasons 
go
CREATE VIEW [dbo].vwReasons 
AS
SELECT     ID, Name,SORT_ORDER
FROM         dbo.REASONS
go

drop view [dbo].vwReasons_List
go
CREATE VIEW [dbo].vwReasons_List
AS
SELECT     ID, NAME, SORT_ORDER
FROM         dbo.vwReasons 
GO


drop view [dbo].vwReasons_EDIT
go
CREATE VIEW [dbo].vwReasons_EDIT
AS
SELECT     ID, NAME 
FROM         dbo.vwReasons
go

drop PROC [dbo].[spREASONS_Update]
go
create PROC [dbo].[spREASONS_Update]
	@ID UNIQUEIDENTIFIER OUTPUT,
	@NAME NVARCHAR(255)
AS
	IF @Id IS NULL
	BEGIN
		SET @ID = NEWID()
		INSERT INTO dbo.Reasons(ID, [NAME])
		VALUES(@ID, @NAME)
	END
	ELSE
	BEGIN
		SET ROWCOUNT 1
		UPDATE dbo.REASONS SET
			[NAME] = @NAME
		WHERE
			ID = @ID
	END
go
------------------------------------------------------------
--------------  EDIT VIEW FOR TOPIC CATEGORIES -------------
	---------- INSERT EDIT VIEW -------
delete EDITVIEWS where MODULE_NAME ='CallReasons'
go
INSERT INTO [dbo].[EDITVIEWS]
           ([ID]
           ,[DELETED]
           ,[DATE_ENTERED]
           ,[DATE_MODIFIED]
           ,[DATE_MODIFIED_UTC]
           ,[NAME]
           ,[MODULE_NAME]
           ,[VIEW_NAME]
           ,[LABEL_WIDTH]
           ,[FIELD_WIDTH]
           )
     VALUES
           (newid()
           ,0
           ,getdate()
           ,getdate()
           ,getdate()
           ,'CallReasons.EditView'
           ,'CallReasons'
           ,'vwReasons_Edit'
           ,'15%'
           ,'35%'
           )
GO
	---------- INSERT FIELD : NAME -------
DELETE EDITVIEWS_FIELDS WHERE EDIT_NAME= 'CallReasons.EditView'
go
INSERT INTO [dbo].[EDITVIEWS_FIELDS]
           ([ID]
           ,[DELETED]
           ,[DATE_ENTERED]
           ,[DATE_MODIFIED]
           ,[DATE_MODIFIED_UTC]
           ,[EDIT_NAME]
           ,[FIELD_INDEX]
           ,[FIELD_TYPE]
           ,[DEFAULT_VIEW]
           ,[DATA_LABEL]
           ,[DATA_FIELD]
           ,[DATA_REQUIRED]
           ,[UI_REQUIRED]
           ,[FORMAT_TAB_INDEX]
           ,[FORMAT_MAX_LENGTH]
           ,[FORMAT_SIZE]
           ,[FORMAT_ROWS]
           ,[FORMAT_COLUMNS]
           ,[COLSPAN]
           )
     VALUES
           (newid()
           ,0
           ,getdate()
           ,getdate()
           ,getdate()
           ,'CallReasons.EditView'
           ,0
           ,'TextBox'
           ,0
           ,'CallReasons.LBL_Name'
           ,'NAME'
           ,1
           ,1
           ,1
           ,200
           ,80
           ,null
           ,NULL
           ,3
           )
GO

--------- INSERT TERMONOLOGY -----------
	
DELETE TERMINOLOGY WHERE [MODULE_NAME]='CallReasons'
GO
INSERT INTO [dbo].[TERMINOLOGY] (ID,[NAME],[MODULE_NAME],[DISPLAY_NAME],[LANG],[DELETED],[DATE_ENTERED],[DATE_MODIFIED],[DATE_MODIFIED_UTC])
VALUES
	   (newid()
	   ,'LBL_Name'
	   ,'CallReasons'
	   ,'Name'
	   ,'en-US',0,getdate(),getdate(),getdate())
GO

INSERT INTO [dbo].[TERMINOLOGY] (ID,[NAME],[MODULE_NAME],[DISPLAY_NAME],[LANG],[DELETED],[DATE_ENTERED],[DATE_MODIFIED],[DATE_MODIFIED_UTC])
VALUES
	   (newid()
	   ,'LNK_CALL_REASON_LIST'
	   ,'CallReasons'
	   ,'Call Reasons'
	   ,'en-US',0,getdate(),getdate(),getdate())
GO
INSERT INTO [dbo].[TERMINOLOGY] (ID,[NAME],[MODULE_NAME],[DISPLAY_NAME],[LANG],[DELETED],[DATE_ENTERED],[DATE_MODIFIED],[DATE_MODIFIED_UTC])
VALUES
	   (newid()
	   ,'LNK_NEW_CALL_REASON'
	   ,'CallReasons'
	   ,'Create Call Reasons'
	   ,'en-US',0,getdate(),getdate(),getdate())
GO
INSERT INTO [dbo].[TERMINOLOGY] (ID,[NAME],[MODULE_NAME],[DISPLAY_NAME],[LANG],[DELETED],[DATE_ENTERED],[DATE_MODIFIED],[DATE_MODIFIED_UTC])
VALUES
	   (newid()
	   ,'LBL_LIST_NAME'
	   ,'CallReasons'
	   ,'Reason'
	   ,'en-US',0,getdate(),getdate(),getdate())
GO
delete TERMINOLOGY where id='349FB1CA-BA40-4187-9F8A-827387DF0EF2'
go
INSERT INTO [dbo].[TERMINOLOGY] (ID,[NAME],LIST_NAME,[DISPLAY_NAME],[LANG],[DELETED],[DATE_ENTERED],[DATE_MODIFIED],[DATE_MODIFIED_UTC])
VALUES
	   ('349FB1CA-BA40-4187-9F8A-827387DF0EF2'
	   ,'CallReasons'
	   ,'moduleList'
	   ,'Call Reasons'
	   ,'en-US',0,getdate(),getdate(),getdate())
GO	
delete TERMINOLOGY where id='34776BFA-310E-4100-A0F5-26C7E61BB2DB'
go
INSERT INTO [dbo].[TERMINOLOGY] (ID,[NAME],module_name,[DISPLAY_NAME],[LANG],[DELETED],[DATE_ENTERED],[DATE_MODIFIED],[DATE_MODIFIED_UTC])
VALUES
	   ('34776BFA-310E-4100-A0F5-26C7E61BB2DB'
	   ,'LBL_CALL_REASON'
	   ,'Administration'
	   ,'Call Reasons'
	   ,'en-US',0,getdate(),getdate(),getdate())
GO	
----------  INSERT DYNAMIC BUTTON -----------------
GO
delete DYNAMIC_BUTTONS where [VIEW_NAME]='CallReasons.EditView'
GO
INSERT INTO [dbo].[DYNAMIC_BUTTONS] ([ID], [DELETED], [CREATED_BY], [DATE_ENTERED], [MODIFIED_USER_ID], [DATE_MODIFIED], [DATE_MODIFIED_UTC], [VIEW_NAME], [CONTROL_INDEX], [CONTROL_TYPE], [DEFAULT_VIEW], [MODULE_NAME], [MODULE_ACCESS_TYPE], [TARGET_NAME], [TARGET_ACCESS_TYPE], [MOBILE_ONLY], [ADMIN_ONLY], [EXCLUDE_MOBILE], [CONTROL_TEXT], [CONTROL_TOOLTIP], [CONTROL_ACCESSKEY], [CONTROL_CSSCLASS], [TEXT_FIELD], [ARGUMENT_FIELD], [COMMAND_NAME], [URL_FORMAT], [URL_TARGET], [ONCLICK_SCRIPT])
Values (newid(), 0, NULL, getdate(), NULL, getdate(),getdate(), N'CallReasons.EditView', 1, N'ButtonLink', 0, N'CallReasons', NULL, NULL, NULL, 0, 0, 0, N'.LBL_CANCEL_BUTTON_LABEL', N'.LBL_CANCEL_BUTTON_TITLE', N'.LBL_CANCEL_BUTTON_KEY', N'button', N'ID', NULL, N'Cancel', N'default.aspx', NULL, NULL)
GO
INSERT INTO [dbo].[DYNAMIC_BUTTONS]([ID], [DELETED], [CREATED_BY], [DATE_ENTERED], [MODIFIED_USER_ID], [DATE_MODIFIED], [DATE_MODIFIED_UTC], [VIEW_NAME], [CONTROL_INDEX], [CONTROL_TYPE], [DEFAULT_VIEW], [MODULE_NAME], [MODULE_ACCESS_TYPE], [TARGET_NAME], [TARGET_ACCESS_TYPE], [MOBILE_ONLY], [ADMIN_ONLY], [EXCLUDE_MOBILE], [CONTROL_TEXT], [CONTROL_TOOLTIP], [CONTROL_ACCESSKEY], [CONTROL_CSSCLASS], [TEXT_FIELD], [ARGUMENT_FIELD], [COMMAND_NAME], [URL_FORMAT], [URL_TARGET], [ONCLICK_SCRIPT])
Values (newid(), 0, NULL, getdate(), NULL, getdate(),getdate(), N'CallReasons.EditView', 0, N'Button', 0, N'CallReasons', N'edit', NULL, NULL, 0, 0, 0, N'.LBL_SAVE_BUTTON_LABEL', N'.LBL_SAVE_BUTTON_TITLE', N'.LBL_SAVE_BUTTON_KEY', N'button', NULL, NULL, N'Save', NULL, NULL, NULL)

GO

drop View [dbo].[vwINBOUNDCALLS_MyList]
go
Create View [dbo].[vwINBOUNDCALLS_MyList]
as
select 
c.ID,
c.DATE_START,
c.FROM_ADDRESS,
c.DURATION_MINUTES ,
cat.Name TOPIC_CATEGORY,
t.NAME TOPIC, 
r.NAME REASON,
c.DESCRIPTION, 
c.ASSIGNED_USER_ID
from CALLS c
left join REASONS r on c.REASON_ID=r.id
left join CallTopics ct on ct.CallId=c.ID
left join TOPICS t on t.ID=ct.TopicId and t.PARENT_ID is not null
left join  TOPICS cat on cat.id = t.PARENT_ID and cat.PARENT_ID is null
GO

--============================================
-- Latest inbound calls
delete [SHORTCUTS] where id='b894b615-26e1-4011-be4c-7f4ae3fc15fd'
INSERT INTO [dbo].[SHORTCUTS]([ID], [DELETED], [CREATED_BY], [DATE_ENTERED], [MODIFIED_USER_ID], [DATE_MODIFIED], [DATE_MODIFIED_UTC], [MODULE_NAME], [DISPLAY_NAME], [RELATIVE_PATH], [IMAGE_NAME], [SHORTCUT_ENABLED], [SHORTCUT_ORDER], [SHORTCUT_MODULE], [SHORTCUT_ACLTYPE])
SELECT N'b894b615-26e1-4011-be4c-7f4ae3fc15fd', 0, NULL, '20110818 23:00:15.540', NULL, '20110818 23:00:15.540', '20110818 16:00:15.540', N'Calls', N'Calls.LNK_LATTEST_INBOUND_CALL', N'~/Calls/LattestInboundCall.aspx', N'Calls.gif', 1, 6, N'Calls', N'edit'
go
delete [dbo].[TERMINOLOGY] where [ID]='2EE972D8-BD4A-40C5-B482-3EDECE6BD8C6'
go
INSERT INTO [dbo].[TERMINOLOGY]([ID], [DELETED], [CREATED_BY], [DATE_ENTERED], [MODIFIED_USER_ID], [DATE_MODIFIED], [DATE_MODIFIED_UTC], [NAME], [LANG], [MODULE_NAME], [LIST_NAME], [LIST_ORDER], [DISPLAY_NAME])
SELECT N'2EE972D8-BD4A-40C5-B482-3EDECE6BD8C6', 0, NULL, '20110818 23:00:18.860', NULL, '20110818 23:00:18.860', '20110818 16:00:18.860', N'LNK_LATTEST_INBOUND_CALL', N'en-US', N'Calls', NULL, NULL, N'Latest Inbound calls'





/* END UPDATE SCRIPT */
go
if @@error<>0 and @@trancount>0 rollback transaction
go

if @@trancount=0 begin
	update #tmpProperties set value = 1 where name = 'error'
	begin transaction
end
go

if (select top 1 value from #tmpProperties where name = 'error') <> '0' rollback transaction
go

if @@trancount > 0 begin
	declare @@scriptName nvarchar(255)
	select top 1 @@scriptName = value from #tmpProperties where name = 'script'

	if dbo.scriptExisted(@@scriptName) = 1 begin
		rollback transaction
		print @@scriptName + ' already executes'
	end
	else begin
		exec dbo.updateScript @@scriptName
		commit transaction
		print 'The database update succeeded'
	end
END
else begin
	print 'The database update failed'
end
go

drop table #tmpProperties
go
